/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package GoodsBean;

import DBConnection.ATeamObject;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import java.util.Date;
import javax.ejb.CreateException;
import javax.ejb.EJBException;
import javax.ejb.EntityBean;
import javax.ejb.EntityContext;
import javax.ejb.NoSuchEntityException;
import javax.ejb.ObjectNotFoundException;
import javax.ejb.RemoveException;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

/**
 *
 * @author XPYCT
 */
public class GoodsBean implements EntityBean {
    private EntityContext entityContext;
    private DataSource dataSource;
    private Connection connection;
    private Integer goodsId;
    private Integer categoryId;
    private Integer manufacturerId;
    private Integer price;
    private String nameOfGoods;
    private String nameOfCategory;
    private String nameOfManufacture;
    private String goodsDescription;
    private Integer number;
    private Date goodsCreateDate;
    private Date goodsLastModifyDate;
    private Boolean isUpdate = false;
       
    public GoodsBean() {
        System.out.println("In GoodsBean");
    }
    
    public void ejbActivate() {
        System.out.println("In ejbPassivate");
    }

    public Integer ejbCreate(ATeamObject ato)
            throws CreateException {
        System.out.println("In ejbCreate");
        
        categoryId = ato.getParentId();
        manufacturerId = ato.getManufacturerId();
        price = ato.getPrice();
        nameOfGoods = ato.getName();
        goodsDescription = ato.getObjectDescription();
        number = ato.getNumber();
              
        String getNextSeq = "SELECT ObjectSeq.nextval FROM dual";
        
        String addInObjectQuery = 
                "INSERT INTO Object"
                + " VALUES(?,?,?,4,?)";
    
        String addInParamsQuery = 
                "INSERT INTO Params VALUES(?,?,?)";
    
        String addInReferenceQuery =
                "INSERT INTO Reference VALUES(?,?,9)";
        
        String getDateQuery = 
                "SELECT TO_DATE(VALUE, 'DD.MM.YYYY HH24:MI:SS') AS VALUE,"
                + " ATTRID FROM PARAMS"
                + " WHERE OBJECTID = ?"
                + " AND ATTRID IN (21,22)"
                + " ORDER BY ATTRID";
        
        String getNameQuery = 
                "SELECT Object.Name"
                + " FROM Object"
                + " WHERE ObjectId = ?"
                + " AND ObjectTypeId = ?";
        
        
        PreparedStatement createStatement = null;
        ResultSet resultSet = null;
        
        try {
            if (connection == null){
                connection = dataSource.getConnection();
                System.out.println("Connection is ready");
            }
            createStatement = connection.prepareStatement(getNextSeq);
            resultSet = createStatement.executeQuery();
            if (!resultSet.next()){
                throw new CreateException();
            }
            goodsId = resultSet.getInt(1);
            ato.setObjectId(resultSet.getInt(1));
            resultSet = null;
                        
            createStatement = connection.prepareStatement(addInObjectQuery);
            createStatement.setInt(1, ato.getObjectId());
            createStatement.setInt(2, ato.getParentId());
            createStatement.setString(3, ato.getName());
            createStatement.setString(4, ato.getObjectDescription()); 
            if (createStatement.executeUpdate() != 1) {
                throw new CreateException(
                        "Impossible to create goods object");
            }
            setDate(createStatement, connection, goodsId);
            
            createStatement = connection.prepareStatement(getNameQuery);
            createStatement.setInt(1, ato.getParentId());
            createStatement.setInt(2, 1);
            resultSet = createStatement.executeQuery(); 
            if (!resultSet.next()){
                throw new CreateException();
            }
            nameOfCategory = resultSet.getString(1);
            resultSet = null;
            
            createStatement.setInt(1, ato.getManufacturerId());
            createStatement.setInt(2, 2);
            resultSet = createStatement.executeQuery(); 
            if (!resultSet.next()){
                throw new CreateException();
            }
            nameOfManufacture = resultSet.getString(1);
            resultSet = null;
            
            createStatement = connection.prepareStatement (
                    addInParamsQuery);
            createStatement.setInt(1, ato.getObjectId());
            createStatement.setInt(2, 10);
            createStatement.setString(
                    3, new Integer(ato.getPrice()).toString());         
            if (createStatement.executeUpdate() != 1) {
                throw new CreateException("Impossible to set goods price");
            }
            
            createStatement.setInt(1, ato.getObjectId());
            createStatement.setInt(2, 11);
            createStatement.setString(3, new Integer(
                    ato.getNumber()).toString());         
            if (createStatement.executeUpdate() != 1) {
                throw new CreateException("Impossible to set goods numder");
            }
            
            createStatement = connection.prepareStatement(getDateQuery);
            createStatement.setInt(1, ato.getObjectId());
            resultSet = createStatement.executeQuery(); 
            if (!resultSet.next()){
                throw new CreateException();
            }
            
            goodsCreateDate = resultSet.getDate("Value");
            resultSet.next();
            goodsLastModifyDate = resultSet.getDate("Value");
            resultSet = null;
            
            createStatement = connection.prepareStatement (
                    addInReferenceQuery);
            createStatement.setInt(1, ato.getManufacturerId());                
            createStatement.setInt(2, ato.getObjectId());                
            if (createStatement.executeUpdate() != 1) {
                throw new CreateException(
                        "Impossible to set goods reference to manufacturer");
            }
            
            createStatement.close();
            closeConnection();
            return ato.getObjectId();
        } catch (SQLException ex) {
            throw new EJBException("ejbCreate:" + ex.getMessage());
        } 
    }
    
    void setDate(
            PreparedStatement statement, Connection connection, Integer goodsId)
            throws SQLException {
        
        String setDateQuery = 
                "INSERT INTO Params"
                + " VALUES(?,?,TO_CHAR(SYSDATE,'DD.MM.YYYY HH24:MI:SS'));";
        
        statement = connection.prepareStatement(setDateQuery);
        statement.setInt(1, goodsId);
        statement.setInt(2, 21);
        if(statement.executeUpdate() != 1){
            throw new NoSuchEntityException(
                    "The goods create date hasn't set");
        }
        
        statement = connection.prepareStatement(setDateQuery);
        statement.setInt(1, goodsId);
        statement.setInt(2, 22);
        if(statement.executeUpdate() != 1){
            throw new NoSuchEntityException(
                    "The goods modify date hasn't set");
        }
        
    }
    
    public Collection ejbFindAllGoods()
            throws ObjectNotFoundException {
        System.out.println("In ejbFindAllGoods");        
        
        PreparedStatement findAllGoodsStatement = null;
        ArrayList goodsIds = new ArrayList();
        String allGoodsQuery =
                "SELECT OBJECT.OBJECTID" +
                " FROM OBJECT" +
                " WHERE OBJECT.OBJECTTYPEID = 4" +
                " ORDER BY OBJECT.OBJECTID";
        
        try{
            if(connection == null){
                connection = dataSource.getConnection();
                System.out.println("Connection is ready");
            }
            findAllGoodsStatement = connection.prepareStatement(
                    allGoodsQuery);
            ResultSet resultSet = findAllGoodsStatement.executeQuery();            
            if (!resultSet.next()){
                throw new ObjectNotFoundException();
            }
            
            do {
                Integer goodId = new Integer(resultSet.getInt("ObjectId"));
                goodsIds.add(goodId);
            } while (resultSet.next());
            
            findAllGoodsStatement.close();
            closeConnection();
            return goodsIds;
        } catch (SQLException e){
            throw new ObjectNotFoundException(e.getMessage());
        } 
    }
    
    public Collection ejbFindAllByCategoryId(ATeamObject ato)
            throws ObjectNotFoundException {
        System.out.println("In ejbFindAllByCategoryId");
        
        PreparedStatement findAllByCategoryIdStatement = null;
        ArrayList goodsIds = new ArrayList();
        
        String AllGoodOfCategoryQuery =
                "SELECT DISTINCT Object.ObjectId" +
                " FROM Object" +
                " WHERE OBJECT.OBJECTTYPEID = 4" +
                " AND Object.ParentId = ?" +
                " ORDER BY ObjectId";
        
        try{
            if(connection == null){
                connection = dataSource.getConnection();
                System.out.println("Connection is ready");
            }
            findAllByCategoryIdStatement = connection.prepareStatement (
                    AllGoodOfCategoryQuery);
            findAllByCategoryIdStatement.setInt(1, ato.getParentId());
            ResultSet resultSet = findAllByCategoryIdStatement.executeQuery();            
            if (!resultSet.next()){
                throw new ObjectNotFoundException();
            }
            
            do {
                Integer goodId = new Integer(resultSet.getInt("ObjectId"));
                goodsIds.add(goodId);
            } while (resultSet.next());
        
            findAllByCategoryIdStatement.close();
            closeConnection();
            return goodsIds;
        } catch (SQLException e) {
            throw new ObjectNotFoundException(e.getMessage());
        }
    }
    
    public Collection ejbFindAllByManufacturerId(ATeamObject ato)
            throws ObjectNotFoundException {
        System.out.println("In ejbFindAllByManufacturerId");
        
        PreparedStatement findAllByManufacturerIdStatement = null;
        ArrayList goodsIds = new ArrayList();
        
        String AllGoodOfManufactureQuery =
                "SELECT Reference.ObjectId" +
                " FROM Reference" +
                " WHERE Reference.ReferenceId = ?" +
                " ORDER BY ObjectId";
                
        try{
            if(connection == null){
                connection = dataSource.getConnection();
                System.out.println("Connection is ready");
            }
            
            findAllByManufacturerIdStatement = connection.prepareStatement (
                    AllGoodOfManufactureQuery);
            findAllByManufacturerIdStatement.setInt(1, ato.getManufacturerId());
            ResultSet resultSet = 
                    findAllByManufacturerIdStatement.executeQuery();            
            if (!resultSet.next()){
                throw new ObjectNotFoundException();
            }
            
            
            do {
                Integer goodId = new Integer(resultSet.getInt("ObjectId"));
                goodsIds.add(goodId);
            } while (resultSet.next());
        
            findAllByManufacturerIdStatement.close();
            closeConnection();
            return goodsIds;
        } catch (SQLException e) {
            throw new ObjectNotFoundException(e.getMessage());
        }
    }
    
    public Collection ejbFindByAllCriteria(ATeamObject ato)
            throws ObjectNotFoundException {
        System.out.println("In ejbFindByAllCriteria");
        
        boolean itFirstCriteria =true;
        boolean findByName=false;
        boolean findByPrice = false;
        boolean findByCategory = false;
        boolean findByManufacture = false;
        boolean findByPriceBetween = false;
        
        PreparedStatement GoodsByAllCriteriaQueryStat = null;
        ArrayList goodsIds = new ArrayList();
        
        String findGoodsByAllCriteriaQuery = 
                "SELECT DISTINCT Params.ObjectID"+
                " FROM Params"+
                " WHERE ";
              
        if (ato.getName()!=null){
            findGoodsByAllCriteriaQuery += 
                    " Params.ObjectId IN (SELECT Object.ObjectId" +
                    " FROM Object"+
                    " WHERE Object.Name LIKE ?)";
            itFirstCriteria = false;
            findByName = true;
        }
        
        if (ato.getPrice()!=0){
            if(!itFirstCriteria){
                findGoodsByAllCriteriaQuery += " AND";
            }
            else{
                itFirstCriteria = false;
            }

            findGoodsByAllCriteriaQuery +=
                    " Params.ObjectId IN (SELECT Params.ObjectId" +
                    " FROM Params"+
                    " WHERE Params.AttrId = 10"+
                    " AND CAST(Params.Value AS INTEGER)";
            
            switch (ato.getTypeOfPrice()){
                case 1:
                    findGoodsByAllCriteriaQuery += " =?)";
                    break;
                case 2:
                    findGoodsByAllCriteriaQuery += " <?)";
                    break;
                case 3:
                    findGoodsByAllCriteriaQuery += " >?)";
                    break;
                case 4:
                    findGoodsByAllCriteriaQuery +=
                            " >? AND CAST(Params.Value AS INTEGER) < ?)";
                    findByPriceBetween=true;
                    break;
            }
            findByPrice = true;
        }
        if(ato.getParentId() != 0){
            if(!itFirstCriteria){
                findGoodsByAllCriteriaQuery += " AND";
            }
            else{
                itFirstCriteria = false;
            }
            
            findGoodsByAllCriteriaQuery +=
                    " Params.ObjectId IN (SELECT Object.ObjectId" +
                    " FROM Object"+
                    " WHERE Object.ParentId = ?)";
            findByCategory = true;
        }
        if(ato.getManufacturerId() != 0){
            if(!itFirstCriteria){
                findGoodsByAllCriteriaQuery += " AND";
            }
            else{
                itFirstCriteria = false;
            }
            
            findGoodsByAllCriteriaQuery +=
                    " Params.ObjectId IN (SELECT Reference.ObjectId" +
                    " FROM Reference"+
                    " WHERE Reference.ReferenceId = ?)";
            findByManufacture = true;
        }
        findGoodsByAllCriteriaQuery += " ORDER BY OBJECTID";
        
        try {
            if(connection == null){
                connection = dataSource.getConnection();
                System.out.println("Connection is ready");
            }
            GoodsByAllCriteriaQueryStat =
                connection.prepareStatement(findGoodsByAllCriteriaQuery);
            int parameterIndex=1;

            if(findByName){
                if(!ato.getContainName()){
                    GoodsByAllCriteriaQueryStat.setString(
                            parameterIndex++, ato.getName());
                }
                else{
                    GoodsByAllCriteriaQueryStat.setString(
                            parameterIndex++, "%" + ato.getName() + "%");
                }
            }

            if(findByPrice){
                GoodsByAllCriteriaQueryStat.setInt(
                        parameterIndex++, ato.getPrice());
                if (findByPriceBetween) {
                    GoodsByAllCriteriaQueryStat.setInt(
                            parameterIndex++, ato.getPriceMax());
                }
            }

            if(findByCategory){
                GoodsByAllCriteriaQueryStat.setInt(
                        parameterIndex++, ato.getParentId());
            }

            if(findByManufacture){
                GoodsByAllCriteriaQueryStat.setInt(
                        parameterIndex++, ato.getManufacturerId());
            }
            ResultSet resultSet = GoodsByAllCriteriaQueryStat.executeQuery();
            if (!resultSet.next()){
                    throw new ObjectNotFoundException();
                }
            do{
                Integer goodId = new Integer(resultSet.getInt("ObjectId"));
                goodsIds.add(goodId);
            }while (resultSet.next());
            
        GoodsByAllCriteriaQueryStat.close();
        closeConnection();
        return goodsIds;
        } catch (SQLException e){
            throw new ObjectNotFoundException(e.getMessage());
        }
    }
    
    public Collection ejbFindByNameEquals(ATeamObject ato)
            throws ObjectNotFoundException {
        System.out.println("In ejbFindByNameEquals"); 
        
        PreparedStatement findByNameEqualsStatement = null;
        ArrayList goodsIds = new ArrayList();
        
        String GoodsByNameEqualsQuery =
            "SELECT ObjectId" +
            " FROM Object" +
            " WHERE Name LIKE ?" +
            " ORDER BY ObjectId";
        
        try{
            if (connection == null){
                connection = dataSource.getConnection();
                System.out.println("Connection is ready");
            }
            
            findByNameEqualsStatement = connection.prepareStatement(
                    GoodsByNameEqualsQuery);
            findByNameEqualsStatement.setString(1, ato.getName());
            ResultSet resultSet = findByNameEqualsStatement.executeQuery();            
            if (!resultSet.next()){
                throw new ObjectNotFoundException();
            }
            
            do {
                Integer goodId = new Integer(resultSet.getInt("ObjectId"));
                goodsIds.add(goodId);
            } while (resultSet.next());
            
            findByNameEqualsStatement.close();
            closeConnection();
            return goodsIds;
        } catch (SQLException e) {
            throw new ObjectNotFoundException(e.getMessage());
        }
    }

    public Collection ejbFindByPriceEquals(ATeamObject ato) 
            throws ObjectNotFoundException {
        System.out.println("In ejbFindByPriceEquals");
        
        PreparedStatement findByPriceEqualsStatement = null;
        ArrayList goodsIds = new ArrayList();
        
        String GoodsByPriceEqualsQuery =
                "SELECT ObjectId" +
                " FROM Object" +
                " WHERE ObjectId IN "
                + "(SELECT ObjectId" +
                " FROM Params" +
                " WHERE AttrId = 8" + 
                " AND Value = ?)" +
                " ORDER BY ObjectId";
        
        try{
            if(connection == null){
                connection = dataSource.getConnection();
                System.out.println("Connection is ready");
            }
            
            findByPriceEqualsStatement = connection.prepareStatement(
                    GoodsByPriceEqualsQuery);
            findByPriceEqualsStatement.setInt(1, ato.getPrice());
            ResultSet resultSet = findByPriceEqualsStatement.executeQuery();
            if (!resultSet.next()){
                throw new ObjectNotFoundException();
            }
            do {
                Integer goodId = new Integer(resultSet.getInt("ObjectId"));
                goodsIds.add(goodId);
            } while (resultSet.next());   
            
            findByPriceEqualsStatement.close();
            closeConnection();
            return goodsIds;
        } catch (SQLException e) {
            throw new ObjectNotFoundException(e.getMessage());
        }
    }
    
    public Integer ejbFindByPrimaryKey (Integer primaryKey) 
            throws ObjectNotFoundException{
        System.out.println("In ejbFindByPrimaryKey"); 
        
        PreparedStatement findByPrimaryKeyStatement = null;
        String findByPrimaryKeyQuery = 
                "SELECT ObjectId"
                + " FROM Object" +
                " WHERE ObjectId = ?" + 
                " AND ObjectTYPEID = 4";
        try {
            if (connection == null) {
                connection = dataSource.getConnection();
                System.out.println("Connection is ready");
            }
            findByPrimaryKeyStatement = connection.prepareStatement(
                    findByPrimaryKeyQuery);
            findByPrimaryKeyStatement.setInt(1, primaryKey);
            ResultSet rs = findByPrimaryKeyStatement.executeQuery();            
            if (!rs.next()){
                throw new ObjectNotFoundException("The goods aren't found");
            }
            else{
                findByPrimaryKeyStatement.close();
                
                closeConnection();
                return primaryKey;
            }
        } catch (SQLException e) {
            throw new ObjectNotFoundException("ejbFindByPrimaryKey:"
                    + e.getMessage());
        }
    }
    
    public void ejbLoad(){
        System.out.println("In ejbLoad");
        
        PreparedStatement loadStatement = null;
        ResultSet resultSet = null;
                
        String getParamsOfGoodsQuery =
                "SELECT Params.AttrId, Params.Value "
                + "FROM Params, OBJECT "
                + "WHERE Params.ObjectId = ?"
                + " AND OBJECT.OBJECTID = ?"
                + " AND OBJECT.OBJECTTYPEID = 4"
                + " AND NOT PARAMS.AttrId IN (21,22)"
                + " ORDER BY AttrId";
        
        String getDateQuery = 
                "SELECT TO_DATE(VALUE, 'DD.MM.YYYY HH24:MI:SS') AS VALUE,"
                + " ATTRID FROM PARAMS"
                + " WHERE OBJECTID = ?"
                + " AND ATTRID IN (21,22)"
                + " ORDER BY ATTRID";
        
        String getGoodsDescriptionQuery =
                "SELECT OBJECT.OBJECTDESCRIPTION" +
                " FROM OBJECT" +
                " WHERE OBJECT.OBJECTID = ?";
        
        String getIdCategoryOfGoodQuery =
                "SELECT OBJECT.PARENTID FROM Object" +
                " WHERE Object.ObjectId = ?" +
                " AND Object.ObjectTYPEID = 4";
        
        String getIdManufacturerOfGoodQuery =
                "SELECT Reference.ReferenceId" +
                " FROM Reference, OBJECT" +
                " WHERE Reference.ObjectId = ?" +
                " AND OBJECT.OBJECTID = ?" +
                " AND OBJECT.OBJECTTYPEID = 4";
        
        String getNameQuery = 
                "SELECT Object.Name"
                + " FROM Object"
                + " WHERE ObjectId = ?"
                + " AND ObjectTypeId = ?";
            
        try {
            if (connection == null) {
                connection = dataSource.getConnection();
                System.out.println("Connection is ready");
            }
            
            goodsId = (Integer)entityContext.getPrimaryKey();
            
            loadStatement = connection.prepareStatement(
                    getParamsOfGoodsQuery);
            loadStatement.setInt(1, goodsId);
            loadStatement.setInt(2, goodsId);
            resultSet = loadStatement.executeQuery(); 
            if (!resultSet.next()){
                throw new NoSuchEntityException("The goods aren't found");
            }
            price = resultSet.getInt("Value");
            resultSet.next();
            number = resultSet.getInt("Value");
            resultSet = null;
            
            loadStatement = connection.prepareStatement(getDateQuery);
            loadStatement.setInt(1, goodsId);
            resultSet = loadStatement.executeQuery(); 
            if (!resultSet.next()){
                throw new NoSuchEntityException("The goods date isn't found");
            }
            goodsCreateDate = resultSet.getDate("Value");
            resultSet.next();
            goodsLastModifyDate = resultSet.getDate("Value");
            resultSet = null;
            
            loadStatement = connection.prepareStatement(
                    getGoodsDescriptionQuery);
            loadStatement.setInt(1, goodsId);
            resultSet = loadStatement.executeQuery(); 
            if (!resultSet.next()){
                throw new NoSuchEntityException(
                        "The goods description isn't found");
            }
            goodsDescription = resultSet.getString("OBJECTDESCRIPTION");
            resultSet = null;
            
            loadStatement = connection.prepareStatement(
                    getIdCategoryOfGoodQuery);
            loadStatement.setInt(1, goodsId);
            resultSet = loadStatement.executeQuery();
            if (!resultSet.next()){
                throw new NoSuchEntityException(
                        "The goods category isn't found");
            }
            categoryId = resultSet.getInt("PARENTID");
            resultSet = null;
            
            loadStatement = connection.prepareStatement(
                    getIdManufacturerOfGoodQuery);
            loadStatement.setInt(1, goodsId);
            loadStatement.setInt(2, goodsId);
            resultSet = loadStatement.executeQuery(); 
            if (!resultSet.next()){
                throw new NoSuchEntityException(
                        "The goods manufacturer isn't found");
            }
            manufacturerId = resultSet.getInt("ReferenceId");
            
            loadStatement = connection.prepareStatement(getNameQuery);
            loadStatement.setInt(1, goodsId);
            loadStatement.setInt(2, 4);
            resultSet = loadStatement.executeQuery(); 
            if (!resultSet.next()){
                throw new NoSuchEntityException(
                        "The goods name isn't found");
            }
            nameOfGoods = resultSet.getString(1);
            resultSet = null;
            
            loadStatement.setInt(1, categoryId);
            loadStatement.setInt(2, 1);
            resultSet = loadStatement.executeQuery(); 
            if (!resultSet.next()){
                throw new NoSuchEntityException(
                        "The goods category name isn't found");
            }
            nameOfCategory = resultSet.getString(1);
            resultSet = null;
            
            loadStatement.setInt(1, manufacturerId);
            loadStatement.setInt(2, 2);
            resultSet = loadStatement.executeQuery(); 
            if (!resultSet.next()){
                throw new NoSuchEntityException(
                        "The goods manufacturer name isn't found");
            }
            nameOfManufacture = resultSet.getString(1);
                        
            resultSet.close();
            loadStatement.close();
            closeConnection();
        } catch (SQLException e) {
            throw new EJBException(e.getMessage());
        }
    }

    public void ejbPassivate() {
        System.out.println("In ejbPassivate");
    }
    
    public void ejbPostCreate(ATeamObject ato)
            throws CreateException {
        System.out.println("In ejbPostCreate");
    }
    
    public void ejbRemove() throws RemoveException {
        System.out.println("In ejbRemove");
        PreparedStatement removeStatement = null;
        String delGoodByIdQuery = 
                "DELETE FROM Object" +
                " WHERE Object.ObjectId = ?"
                + " AND OBJECT.OBJECTTYPEID = 4";
    
        try {
            if (connection == null) {
                connection = dataSource.getConnection();
                System.out.println("Connection is ready");
            }
            removeStatement = connection.prepareStatement (
                    delGoodByIdQuery);
            removeStatement.setInt(1, goodsId);
            removeStatement.execute();
            
            removeStatement.close();
            closeConnection();
        } catch (SQLException e) {
            throw new EJBException("ejbRemove:" + e.getMessage());
        }
    }
    
    public void ejbStore() {
        
        PreparedStatement storeStatement = null;
        
        String editCategoryOfGoodInObjectQuery =
                "UPDATE Object"+ 
                " SET Object.ParentId = ?"+
                " WHERE Object.ObjectId = ?"
                + " AND OBJECT.OBJECTTYPEID = 4";
        
        String editGoodsDescriptionQuery =
               "UPDATE OBJECT" +
               " SET OBJECT.OBJECTDESCRIPTION = ?" +
               " WHERE OBJECT.OBJECTID = ?" +
               " AND OBJECT.OBJECTTYPEID = 4";
        
        String editManufactureOfGoodInReferenceQuery = 
                "UPDATE Reference"+
                " SET Reference.ReferenceId = ?"+
                " WHERE Reference.ObjectId = ?";

        String editNameOfGoodInObjectQuery = 
                "UPDATE Object"+ 
                " SET Object.Name = ?"+
                " WHERE Object.ObjectId = ?"
                + " AND OBJECT.OBJECTTYPEID = 4";

        String editNumberOfGoodQuery = 
                "UPDATE Params"+ 
                " SET Params.Value = ?"+
                " WHERE Params.ObjectId = ?"+
                " AND Params.AttrId = 11";

        String editPriceOfGoodQuery = 
                "UPDATE Params"+ 
                " SET Params.Value = ?"+
                " WHERE Params.ObjectId = ?"+
                " AND Params.AttrId = 10";
        
        if (isUpdate){
            System.out.println("In ejbStore");
                
            try {

                if (connection == null) {
                    connection = dataSource.getConnection();
                    System.out.println("Connection is ready");
                }

                storeStatement = connection.prepareStatement (
                        editCategoryOfGoodInObjectQuery);
                storeStatement.setInt(1, categoryId);
                storeStatement.setInt(2, goodsId);
                if(storeStatement.executeUpdate() != 1){
                    throw new NoSuchEntityException(
                            "The goods category isn't found");
                }
                updateDate(storeStatement, connection, goodsId);

                storeStatement = connection.prepareStatement (
                        editManufactureOfGoodInReferenceQuery);
                storeStatement.setInt(1, manufacturerId);
                storeStatement.setInt(2, goodsId);
                if(storeStatement.executeUpdate() != 1){
                    throw new NoSuchEntityException(
                            "The goods manufacturer isn't found");
                }
                updateDate(storeStatement, connection, goodsId);

                storeStatement = connection.prepareStatement (
                        editNameOfGoodInObjectQuery);
                storeStatement.setString(1, nameOfGoods);                
                storeStatement.setInt(2, goodsId);
                if(storeStatement.executeUpdate() != 1){
                    throw new NoSuchEntityException("The goods aren't found");
                }
                updateDate(storeStatement, connection, goodsId);

                storeStatement = connection.prepareStatement (
                        editPriceOfGoodQuery);
                storeStatement.setString(1, price.toString());
                storeStatement.setInt(2, goodsId);
                if(storeStatement.executeUpdate() != 1){
                    throw new NoSuchEntityException("The goods price isn't found");
                }
                updateDate(storeStatement, connection, goodsId);

                storeStatement = connection.prepareStatement (
                        editNumberOfGoodQuery);
                storeStatement.setString(1, number.toString());
                storeStatement.setInt(2, goodsId);
                if(storeStatement.executeUpdate() != 1){
                    throw new NoSuchEntityException("The goods number isn't found");
                }
                updateDate(storeStatement, connection, goodsId);

                storeStatement = connection.prepareStatement(
                        editGoodsDescriptionQuery);
                storeStatement.setString(1, goodsDescription);
                storeStatement.setInt(2, goodsId);
                if(storeStatement.executeUpdate() != 1){
                    throw new NoSuchEntityException(
                            "The goods dscription isn't found");
                }
                updateDate(storeStatement, connection, goodsId);

                storeStatement.close();
                closeConnection();
            } catch (SQLException e){
                throw new EJBException("ejbStore:" + e.getMessage());
            }
            isUpdate = false;
        }
    }
    
    void closeConnection() {
        try {
            if (connection != null){
                connection.close();
                System.out.println("Connection closed");
            }
        } catch (SQLException e) {
            throw new EJBException("closeConnection:" + e.getMessage());
        } finally {
            connection = null;
        }
    }
    
    void updateDate(
            PreparedStatement statement, Connection connection, Integer goodsId)
            throws SQLException {
        
        String updateDateQuery = 
                "UPDATE Params"
                + " SET VALUE = TO_CHAR(SYSDATE,'DD.MM.YYYY HH24:MI:SS')"
                + " WHERE ObjectId = ?"
                + " AND ATTRID = 22";
        
        statement = connection.prepareStatement(updateDateQuery);
        statement.setInt(1, goodsId);
        if(statement.executeUpdate() != 1){
            throw new NoSuchEntityException(
                    "The goods modify date isn't found");
        }
    }
    
    public Date getGoodsCreateDate(){
        return goodsCreateDate;
    }
        
    public String getGoodsDescription(){
        return goodsDescription;
    }
    
    public int getGoodsId(){
        return goodsId;
    }
    
    public Date getGoodsLastModifyDate(){
        return goodsLastModifyDate;
    }
    
    public int getCategoryId(){
        return categoryId;
    }
    
    public int getManufactureId(){
        return manufacturerId;
    }
    
    public int getPrice(){
        return price;
    }
    
    public String getNameOfGoods(){
        return nameOfGoods;
    }
    
    public String getNameOfCategory(){
        return nameOfCategory;
    }
    
    public String getNameOfManufacture(){
        return nameOfManufacture;
    }
    
    public int getNumber(){
        return number;
    }

    public void setEntityContext(EntityContext entityContext) {
        System.out.println("In setEntityContext");
        this.entityContext = entityContext;
        try {
            InitialContext initialContext = new InitialContext();
            dataSource = (DataSource) initialContext.lookup(
                    "java:/ATeamConnectionPool");
            System.out.println("dataSource is found");                
        } catch (NamingException e){
            throw new EJBException("setEntityContext:" + e.getMessage());
        }
        if (connection == null){
            try {
                connection = dataSource.getConnection();
                System.out.println("Connection is ready");
            } catch (SQLException e){
                throw new EJBException("setEntityContext:" + e.getMessage());
            }
        }    
    }

    public void setGoodsDescription(String goodsDescription){
        this.goodsDescription = goodsDescription;
        isUpdate = true;
    }
    
    public void setGoodsId(Integer goodsId){
        this.goodsId = goodsId;
        isUpdate = true;
    }
    
    public void setCategoryId(Integer categoryId){
        this.categoryId = categoryId;
        isUpdate = true;
    }
    
    public void setManufactureId(Integer manufactureId){
        this.manufacturerId = manufactureId;
        isUpdate = true;
    }
    
    public void setPrice(Integer price){
        this.price = price;
        isUpdate = true;
    }
    
    public void setNameOfGoods(String nameOfGoods){
        this.nameOfGoods = nameOfGoods;
        isUpdate = true;
    }
    
    public void setNameOfCategory(String nameOfCategory){
        this.nameOfCategory = nameOfCategory;
        isUpdate = true;
    }
    
    public void setNameOfManufacture(String nameOfManufacture){
        this.nameOfManufacture = nameOfManufacture;
        isUpdate = true;
    }
    
    public void setNumber(Integer number){
        this.number = number;
        isUpdate = true;
    }
    
    public void unsetEntityContext() {
        System.out.println("In unsetEntityContext");
        entityContext = null;
        if(connection != null){
            try{
                connection.close();
            }
            catch(SQLException sqlException){
                throw new EJBException(sqlException);
            }
            finally{
                connection = null;
            }
        }     
    }    
}
